/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package th.com.ku;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import th.com.db.connect.PgConnect;
import th.com.entity.BranchEntity;
import th.com.entity.ConfirmTeachTypeEntity;
import th.com.entity.ConfirmclassEntity;
import th.com.entity.CourseEntity;
import th.com.entity.DepartmentEntity;
import th.com.entity.FacultyEntity;
import th.com.entity.LocationEntity;
import th.com.entity.MakeupclassEntity;
import th.com.entity.ProgramEntity;
import th.com.entity.ScheduleEntity;
import th.com.entity.ScheduleTable;
import th.com.entity.SemesterEntity;
import th.com.entity.SubjectEntity;
import th.com.entity.UserEntity;
import th.com.entity.UserTypeEntity;

/**
 *
 * @author Ketsaraporn
 */
public class CAAService {

    /**
     * Web service operation
     */
    public static String createUser(Object userEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        UserEntity entity = (UserEntity) userEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();


            sqlBuffer.append("INSERT INTO \"USER\" (\"USE_ID\", \"USE_Name\", \"USE_Password\", \"USE_Tittle\", \"USE_Firstname\", \"USE_Lastname\", \"USE_Position\", \"FAC_ID\", \"DEP_ID\", \"USER_TYPE_ID\", \"USE_CreatedBy\", \"USE_CreatedTime\")");
            sqlBuffer.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, entity.getUseId());
            pstmt.setString(2, entity.getUseName());
            pstmt.setString(3, entity.getUsePassword());
            pstmt.setString(4, entity.getUseTitle());
            pstmt.setString(5, entity.getUseFirstName());
            pstmt.setString(6, entity.getUseLastName());
            pstmt.setString(7, entity.getUsePosition());
            pstmt.setString(8, entity.getFacId());
            pstmt.setString(9, entity.getDepId());
            pstmt.setInt(10, entity.getUserTypeId());
            pstmt.setString(11, entity.getUseCreatedBy());
            pstmt.setTimestamp(12, new Timestamp(new Date().getTime()));



            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String updateUser(Object userEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        UserEntity entity = (UserEntity) userEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("UPDATE \"USER\" SET \"USE_Name\"=?, \"USE_Tittle\"=?, \"USE_Firstname\"=?, \"USE_Lastname\"=?, \"USE_Position\"=?, \"FAC_ID\"=?, \"DEP_ID\"=?, \"USER_TYPE_ID\"=?, \"USE_ModifyBy\"=?, \"USE_ModifyTime\"=? ");
            sqlBuffer.append("WHERE \"USE_ID\" = ? ");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, entity.getUseName());
            pstmt.setString(2, entity.getUseTitle());
            pstmt.setString(3, entity.getUseFirstName());
            pstmt.setString(4, entity.getUseLastName());
            pstmt.setString(5, entity.getUsePosition());
            pstmt.setString(6, entity.getFacId());
            pstmt.setString(7, entity.getDepId());
            pstmt.setInt(8, entity.getUserTypeId());
            pstmt.setString(9, entity.getUseModifyBy());
            pstmt.setTimestamp(10, new Timestamp(new Date().getTime()));
            pstmt.setString(11, entity.getUseId());


            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String deleteUser(String userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("DELETE FROM \"USER\"WHERE \"USE_ID\"=?");
            pstmt = conn.prepareStatement(sqlBuffer.toString());
            
            pstmt.setString(1, userId);
            

            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String createSubject(Object subjectEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        SubjectEntity entity = (SubjectEntity) subjectEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("INSERT INTO \"SUBJECT\" (\"PRO_ID\", \"SUB_ID\", \"SUB_NameThai\", \"SUB_NameEng\", \"SUB_CreditType\", \"SUB_Credit\", ");
            sqlBuffer.append("\"SUB_DescriptionThai\", \"SUB_DescriptionEng\", \"SUB_CreatedBy\", \"SUB_CreatedTime\") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, entity.getProId());
            pstmt.setString(2, entity.getSubId());
            pstmt.setString(3, entity.getSubNameThai());
            pstmt.setString(4, entity.getSubNameEng());
            pstmt.setString(5, entity.getSubCreditType());
            pstmt.setString(6, entity.getSubCredit());
            pstmt.setString(7, entity.getSubDescriptionThai());
            pstmt.setString(8, entity.getSubDescriptionEng());
            pstmt.setString(9, entity.getSubCreatedBy());
            pstmt.setTimestamp(10, new Timestamp(new Date().getTime()));


            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String updateSubject(Object subjectEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        SubjectEntity entity = (SubjectEntity) subjectEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("UPDATE \"SUBJECT\" SET \"PRO_ID\"=?, \"SUB_NameThai\"=?, \"SUB_NameEng\"=?, \"SUB_CreditType\"=?, \"SUB_Credit\"=?, \"SUB_DescriptionThai\"=?, \"SUB_DescriptionEng\"=?, \"SUB_ModifyBy\"=?, \"SUB_ModifyTime\"=? ");
            sqlBuffer.append("WHERE SUB_ID=?;");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, entity.getProId());
            pstmt.setString(2, entity.getSubNameThai());
            pstmt.setString(3, entity.getSubNameEng());
            pstmt.setString(4, entity.getSubCreditType());
            pstmt.setString(5, entity.getSubCredit());
            pstmt.setString(6, entity.getSubDescriptionThai());
            pstmt.setString(7, entity.getSubDescriptionEng());
            pstmt.setString(8, entity.getSubModifyBy());
            pstmt.setTimestamp(9, new Timestamp(new Date().getTime()));


            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String deleteSubject(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("DELETE FROM \"SUBJECT\" WHERE \"SUB_ID\"=?");
            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, id);

            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String createFaculty(Object facultyEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        FacultyEntity entity = (FacultyEntity) facultyEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("INSERT INTO \"FACULTY\" (\"FAC_ID\", \"FAC_Name\", \"FAC_CreatedBy\", \"FAC_CreatedTime\")");
            sqlBuffer.append(" VALUES (?, ?, ?, ?)");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, entity.getFacId());
            pstmt.setString(2, entity.getFacName());
            pstmt.setString(3, entity.getFacCreatedBy());
            pstmt.setTimestamp(4, new Timestamp(new Date().getTime()));



            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String deleteFaculty(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("DELETE FROM \"FACULTY\" WHERE \"FAC_ID\"=?");
            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, id);
            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String createDepartment(Object departmentEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        DepartmentEntity entity = (DepartmentEntity) departmentEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("INSERT INTO \"DEPARTMENT\" (\"DEP_ID\", \"FAC_ID\", \"DEP_Name\", \"DEP_CreatedBy\", \"DEP_CreatedTime\")");
            sqlBuffer.append(" VALUES (?, ?, ?, ?, ?)");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, entity.getDepId());
            pstmt.setString(2, entity.getFacId());
            pstmt.setString(3, entity.getDepName());
            pstmt.setString(4, entity.getDepCreatedBy());
            pstmt.setTimestamp(5, new Timestamp(new Date().getTime()));



            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String deleteDepertment(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("DELETE FROM \"DEPARTMENT\" WHERE \"DEP_ID\"=?");
            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, id);
            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String createBranch(Object branchEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        BranchEntity entity = (BranchEntity) branchEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("INSERT INTO \"BRANCH\" (\"BRA_ID\", \"DEP_ID\", \"BRA_Name\", \"BRA_CreatedBy\", \"BRA_CreatedTime\")");
            sqlBuffer.append(" VALUES (?, ?, ?, ?, ?)");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, entity.getBraId());
            pstmt.setString(2, entity.getDepId());
            pstmt.setString(3, entity.getBraName());
            pstmt.setString(4, entity.getBraCreatedBy());
            pstmt.setTimestamp(5, new Timestamp(new Date().getTime()));


            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String deleteBranch(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("DELETE FROM \"BRANCH\" WHERE \"BRA_ID\"=?");
            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, id);

            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String createProgram(Object programEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        ProgramEntity entity = (ProgramEntity) programEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("INSERT INTO \"PROGRAM\" (\"BRA_ID\", \"PRO_ID\", \"PRO_Name\", \"PRO_CreatedBy\", \"PRO_CreatedTime\")");
            sqlBuffer.append(" VALUES (?, ?, ?, ?, ?)");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, entity.getBraId());
            pstmt.setString(2, entity.getProId());
            pstmt.setString(3, entity.getProName());
            pstmt.setString(4, entity.getProCreatedBy());
            pstmt.setTimestamp(5, new Timestamp(new Date().getTime()));


            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String deleteProgram(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("DELETE FROM \"PROGRAM\" WHERE \"PRO_ID\"=?");
            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, id);
            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }
    
    public static String createCourse(CourseEntity courseEntity,SemesterEntity semesterEntity, ArrayList<ScheduleEntity> list) {
        Connection conn = null;
        String result;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            
            ResultSet keyRs = createSemester(conn, semesterEntity);
            keyRs.next();
            int key = keyRs.getInt("SEM_ID");
            courseEntity.setSemId(key);
            
            keyRs = creCourse(conn, courseEntity);
            keyRs.next();
            key = keyRs.getInt(1);
            
            courseEntity.setCouId(key);
            createCourseUser(conn, courseEntity, list);

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String updateCourse(CourseEntity courseEntity,SemesterEntity semesterEntity, ArrayList<ScheduleEntity> list) {
        Connection conn = null;
        String result;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            
            updateSemester(conn, semesterEntity);
            
            upCourse(conn, courseEntity);
 
            updateCourseUser(conn, courseEntity, list);

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }
    
    private static void updateCourseUser(Connection conn, CourseEntity courseEntity, ArrayList<ScheduleEntity> list) throws SQLException {
            StringBuffer sqlBuffer = new StringBuffer();
            StringBuffer schBuffer = new StringBuffer();
            PreparedStatement pstmt = null;
            PreparedStatement pstmtSch = null;
            
            sqlBuffer.append("UPDATE \"COURSE_USER\" SET \"COU_ID\" = ?, \"USE_ID\" = ?, \"RightAmount\" = ?, \"AmountActuallyReceived\" = ? ");
            sqlBuffer.append("WHERE \"COU_USE_ID\" = ? ");
            
            schBuffer.append("UPDATE \"SCHEDULE\" SET \"COU_ID\" = ?, \"SCH_BeginTime\" = ?, \"SCH_EndTime\" = ?, \"SCH_TeachDate\" = ?, ");
            schBuffer.append("\"SCH_ModifyBy\" = ?, \"SCH_ModifyTime\" = ?, \"COU_USE_ID\" = ? ");
            schBuffer.append("WHERE \"SCH_ID\" = ? ");
            
            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmtSch = conn.prepareStatement(schBuffer.toString());
            
            for(ScheduleEntity entity : list) {
                pstmt.setInt(1, courseEntity.getCouId());
                pstmt.setString(2, entity.getUserId());
                pstmt.setDouble(3, courseEntity.getCouPricePerTime());
                pstmt.setDouble(4, 0.0);
                pstmt.setInt(5, Integer.valueOf(entity.getCouUseId()));

                pstmt.executeUpdate();
                
                pstmtSch.setInt(1, courseEntity.getCouId());
                pstmtSch.setString(2, entity.getSchBeginTime());
                pstmtSch.setString(3, entity.getSchEndTime());
                pstmtSch.setDate(4, entity.getSchTeachDate());
                pstmtSch.setString(5, courseEntity.getCouModifyBy());
                pstmtSch.setTimestamp(6, new Timestamp(new Date().getTime()));
                pstmtSch.setInt(7, Integer.valueOf(entity.getCouUseId()));
                pstmtSch.setInt(8, entity.getSchId());
                
                pstmtSch.executeUpdate();
            }
            
    }
    
    private static void createCourseUser(Connection conn, CourseEntity courseEntity, ArrayList<ScheduleEntity> list) throws SQLException {
            StringBuffer sqlBuffer = new StringBuffer();
            StringBuffer schBuffer = new StringBuffer();
            PreparedStatement pstmt = null;
            PreparedStatement pstmtSch = null;
            
            sqlBuffer.append("INSERT INTO \"COURSE_USER\" (\"COU_ID\", \"USE_ID\", \"RightAmount\", \"AmountActuallyReceived\") ");
            sqlBuffer.append("VALUES (?, ?, ?, ?)");
            
            schBuffer.append("INSERT INTO \"SCHEDULE\" (\"COU_ID\", \"SCH_BeginTime\", \"SCH_EndTime\", \"SCH_TeachDate\", ");
            schBuffer.append("\"SCH_CreatedBy\", \"SCH_CreatedTime\", \"COU_USE_ID\") ");
            schBuffer.append(" VALUES (?, ?, ?, ?, ?, ?, ?)");
            
            pstmt = conn.prepareStatement(sqlBuffer.toString(), PreparedStatement.RETURN_GENERATED_KEYS);
            pstmtSch = conn.prepareStatement(schBuffer.toString());
            
            for(ScheduleEntity entity : list) {
                pstmt.setInt(1, courseEntity.getCouId());
                pstmt.setString(2, entity.getUserId());
                pstmt.setDouble(3, courseEntity.getCouPricePerTime());
                pstmt.setDouble(4, 0.0);

                pstmt.executeUpdate();
                ResultSet keyRs = pstmt.getGeneratedKeys();
                keyRs.next();
                int key = keyRs.getInt("COU_USE_ID");
                
                pstmtSch.setInt(1, courseEntity.getCouId());
                pstmtSch.setString(2, entity.getSchBeginTime());
                pstmtSch.setString(3, entity.getSchEndTime());
                pstmtSch.setDate(4, entity.getSchTeachDate());
                pstmtSch.setString(5, courseEntity.getCouCreatedBy());
                pstmtSch.setTimestamp(6, new Timestamp(new Date().getTime()));
                pstmtSch.setInt(7, key);
                
                pstmtSch.executeUpdate();
            }
            
    }
    
    private static ResultSet creCourse(Connection conn, CourseEntity entity) throws SQLException {
            StringBuffer sqlBuffer = new StringBuffer();
            PreparedStatement pstmt = null;

            sqlBuffer.append("INSERT INTO \"COURSE\" (\"SUB_ID\", \"SEM_ID\", \"LOC_ID\", \"COU_BeginDate\", ");
            sqlBuffer.append("\"COU_EndDate\", \"COU_DaySun\", \"COU_DayMon\", \"COU_DayTue\", \"COU_DayWed\", \"COU_DayThu\", ");
            sqlBuffer.append("\"COU_DayFri\", \"COU_DaySat\", \"COU_BeginTime\", \"COU_EndTime\", \"COU_Duration\", ");
            sqlBuffer.append("\"COU_PricePerCourse\", \"COU_PricePerTime\", \"COU_CreatedBy\", \"COU_CreatedTime\")");
            sqlBuffer.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

            pstmt = conn.prepareStatement(sqlBuffer.toString(), PreparedStatement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, entity.getSubId());
            pstmt.setInt(2, entity.getSemId());
            pstmt.setInt(3, entity.getLocId());
            pstmt.setDate(4, new java.sql.Date(entity.getCouBeginDate().getTime()));
            pstmt.setDate(5, new java.sql.Date(entity.getCouEndDate().getTime()));
            pstmt.setString(6, entity.getCouDaySun());
            pstmt.setString(7, entity.getCouDayMon());
            pstmt.setString(8, entity.getCouDayTue());
            pstmt.setString(9, entity.getCouDayWed());
            pstmt.setString(10, entity.getCouDayThu());
            pstmt.setString(11, entity.getCouDayFri());
            pstmt.setString(12, entity.getCouDaySat());
            pstmt.setString(13, entity.getCouBeginTime());
            pstmt.setString(14, entity.getCouEndTime());
            pstmt.setString(15, entity.getCouDuration());
            pstmt.setDouble(16, entity.getCouPricePerCourse());
            pstmt.setDouble(17, entity.getCouPricePerTime());
            pstmt.setString(18, entity.getCouCreatedBy());
            pstmt.setTimestamp(19, new Timestamp(new Date().getTime()));

            pstmt.executeUpdate();

            return pstmt.getGeneratedKeys();
    }

    private static void upCourse(Connection conn, CourseEntity entity) throws SQLException {
            StringBuffer sqlBuffer = new StringBuffer();
            PreparedStatement pstmt = null;

            sqlBuffer.append("UPDATE \"COURSE\" SET \"SUB_ID\" = ?, \"SEM_ID\" = ?, \"LOC_ID\" = ?, \"COU_BeginDate\" = ?, ");
            sqlBuffer.append("\"COU_EndDate\" = ?, \"COU_DaySun\" = ?, \"COU_DayMon\" = ?, \"COU_DayTue\" = ?, \"COU_DayWed\" = ?, \"COU_DayThu\" = ?, ");
            sqlBuffer.append("\"COU_DayFri\" = ?, \"COU_DaySat\" = ?, \"COU_BeginTime\" = ?, \"COU_EndTime\" = ?, \"COU_Duration\" = ?, ");
            sqlBuffer.append("\"COU_PricePerCourse\" = ?, \"COU_PricePerTime\" = ?, \"COU_ModifyBy\" = ?, \"COU_ModifyTime\" = ? ");
            sqlBuffer.append("WHERE \"COU_ID\" = ? ");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, entity.getSubId());
            pstmt.setInt(2, entity.getSemId());
            pstmt.setInt(3, entity.getLocId());
            pstmt.setDate(4, new java.sql.Date(entity.getCouBeginDate().getTime()));
            pstmt.setDate(5, new java.sql.Date(entity.getCouEndDate().getTime()));
            pstmt.setString(6, entity.getCouDaySun());
            pstmt.setString(7, entity.getCouDayMon());
            pstmt.setString(8, entity.getCouDayTue());
            pstmt.setString(9, entity.getCouDayWed());
            pstmt.setString(10, entity.getCouDayThu());
            pstmt.setString(11, entity.getCouDayFri());
            pstmt.setString(12, entity.getCouDaySat());
            pstmt.setString(13, entity.getCouBeginTime());
            pstmt.setString(14, entity.getCouEndTime());
            pstmt.setString(15, entity.getCouDuration());
            pstmt.setDouble(16, entity.getCouPricePerCourse());
            pstmt.setDouble(17, entity.getCouPricePerTime());
            pstmt.setString(18, entity.getCouModifyBy());
            pstmt.setTimestamp(19, new Timestamp(new Date().getTime()));
            pstmt.setInt(20, entity.getCouId());

            pstmt.executeUpdate();

    }

    public static String deleteCourse(int couId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        PreparedStatement pstmt2 = null;
        PreparedStatement pstmt3 = null;
        String result;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();
            
            sqlBuffer = new StringBuffer();
            sqlBuffer.append("DELETE FROM \"SCHEDULE\" WHERE \"COU_ID\" = ? ");
            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setInt(1, couId);
            pstmt.executeUpdate();
            
            sqlBuffer = new StringBuffer();
            sqlBuffer.append("DELETE FROM \"COURSE_USER\" WHERE \"COU_ID\" = ? ");
            pstmt2 = conn.prepareStatement(sqlBuffer.toString());
            pstmt2.setInt(1, couId);
            pstmt2.executeUpdate();
            
            sqlBuffer = new StringBuffer();
            sqlBuffer.append("DELETE FROM \"COURSE\" WHERE \"COU_ID\" = ? ");
            pstmt3 = conn.prepareStatement(sqlBuffer.toString());
            pstmt3.setInt(1, couId);
            pstmt3.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }
    
    private static ResultSet createSemester(Connection conn, SemesterEntity semesterEntity) {
        PreparedStatement pstmt = null;
        String result;
        ResultSet keyRS = null;
        
        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();

            sqlBuffer.append("INSERT INTO \"SEMESTER\" (\"SEM_AcademicYear\", \"SEM_Semester\", \"SEM_CreatedBy\", \"SEM_CreatedTime\")");
            sqlBuffer.append(" VALUES (?, ?, ?, ?)");

            pstmt = conn.prepareStatement(sqlBuffer.toString(), PreparedStatement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, semesterEntity.getSemAcademicYear());
            pstmt.setString(2, semesterEntity.getSemSemester());
            pstmt.setString(3, semesterEntity.getSemCreatedBy());
            pstmt.setTimestamp(4, new Timestamp(new Date().getTime()));

            pstmt.executeUpdate();
            keyRS = pstmt.getGeneratedKeys();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return keyRS;
    }

    private static void updateSemester(Connection conn, SemesterEntity semesterEntity) {
        PreparedStatement pstmt = null;
        String result;
        ResultSet keyRS = null;
        
        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();

            sqlBuffer.append("UPDATE \"SEMESTER\" SET \"SEM_AcademicYear\" = ?, \"SEM_Semester\" = ?, ");
            sqlBuffer.append("\"SEM_ModifyBy\" = ?, \"SEM_ModifyTime\" = ? ");
            sqlBuffer.append("WHERE \"SEM_ID\" = ? ");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, semesterEntity.getSemAcademicYear());
            pstmt.setString(2, semesterEntity.getSemSemester());
            pstmt.setString(3, semesterEntity.getSemModifyBy());
            pstmt.setTimestamp(4, new Timestamp(new Date().getTime()));
            pstmt.setInt(5, semesterEntity.getSemId());

            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

    }

    public static String updateSemester(Object semesterEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        SemesterEntity entity = (SemesterEntity) semesterEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("UPDATE \"SEMESTER\" SET \"SEM_AcademicYear\"=?, \"SEM_Semester\"=?, \"SEM_ModifyBy\"=?, \"SEM_ModifyTime\"=?");
            sqlBuffer.append("WHERE SEM_ID=?;");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, entity.getSemAcademicYear());
            pstmt.setString(2, entity.getSemSemester());
            pstmt.setString(3, entity.getSemModifyBy());
            pstmt.setTimestamp(4, new Timestamp(new Date().getTime()));

            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String deleteSemester(Object semesterEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        SemesterEntity entity = (SemesterEntity) semesterEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("DELETE FROM \"SEMESTER\" WHERE SEM_ID=?");


            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String createLocation(Object locationEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        LocationEntity entity = (LocationEntity) locationEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("INSERT INTO \"LOCATION\" (\"LOC_Room\", \"LOC_CreatedBy\", \"LOC_CreatedTime\")");
            sqlBuffer.append(" VALUES (?, ?, ?)");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, entity.getLocRoom());
            pstmt.setString(2, entity.getLocCreatedBy());
            pstmt.setTimestamp(3, new Timestamp(new Date().getTime()));


            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

   
        public static String deleteLocation(int locationId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();

            sqlBuffer.append("DELETE FROM \"LOCATION\"WHERE \"LOC_ID\"=?");
            pstmt = conn.prepareStatement(sqlBuffer.toString());
            
            pstmt.setInt(1, locationId);            
            
            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    
    public static String createConfirmclass(ConfirmclassEntity entity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();

            sqlBuffer.append("INSERT INTO \"CONFIRMCLASS\" (\"SCH_ID\", \"CONF_CreatedBy\", \"CONF_CreatedTime\") ");
            sqlBuffer.append("VALUES (?, ?, ?) ");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setInt(1, entity.getSchID());
            pstmt.setString(2, entity.getConfCreatedBy());
            pstmt.setTimestamp(3, new Timestamp(new Date().getTime()));
            
            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }
    
    public static String createMakeupclass(MakeupclassEntity entity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();

            sqlBuffer.append("INSERT INTO \"MAKEUPCLASS\" (\"SCH_ID\", \"MARC_CreatedBy\", \"MARC_CreatedTime\") ");
            sqlBuffer.append("VALUES (?, ?, ?) ");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setInt(1, entity.getSchId());
            pstmt.setString(2, entity.getMarcCreatedBy());
            pstmt.setTimestamp(3, new Timestamp(new Date().getTime()));
            
            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String updateConfirmclass(Object confirmclassEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        ConfirmclassEntity entity = (ConfirmclassEntity) confirmclassEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("UPDATE \"CONFIRMCLASS\" SET \"SCH_ID\"=?, \"CONF_TeachDate\"=?, \"CONF_Amount\"=?, \"CONF_ModifyBy\"=?, \"CONF_ModifyTime\"=?");
            sqlBuffer.append("WHERE CONF_ID=?;");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setInt(1, entity.getSchID());
            pstmt.setDate(2, new java.sql.Date(entity.getConfTeachDate().getTime()));
            pstmt.setDouble(3, entity.getConfAmount());
            pstmt.setString(4, entity.getConfModifyBy());
            pstmt.setTimestamp(5, new Timestamp(new Date().getTime()));


            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String deleteConfirmclass(Object confirmclassEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        ConfirmclassEntity entity = (ConfirmclassEntity) confirmclassEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("DELETE FROM \"CONFIRMCLASS\" WHERE CONF_ID=?");


            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String createSchedule(Object scheduleEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        ScheduleEntity entity = (ScheduleEntity) scheduleEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("INSERT INTO \"SCHEDULE\" (\"SCH_ID\", \"COU_ID\", \"CONFIRMTEACH_TYPE_ID\", \"SCH_BeginTime\", \"SCH_EndTime\", \"SCH_TeachDate\", \"SCH_CreatedBy\", \"SCH_CreatedTime\")");
            sqlBuffer.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setInt(1, entity.getSchId());
            pstmt.setInt(2, entity.getCouId());
            pstmt.setInt(3, entity.getConfirmTeachTypeId());
            pstmt.setString(4, entity.getSchBeginTime());
            pstmt.setString(5, entity.getSchEndTime());
            pstmt.setDate(6, new java.sql.Date(entity.getSchTeachDate().getTime()));
            pstmt.setString(7, entity.getSchCreatedBy());
            pstmt.setTimestamp(8, new Timestamp(new Date().getTime()));



            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String updateSchedule(Object scheduleEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        ScheduleEntity entity = (ScheduleEntity) scheduleEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("UPDATE \"SCHEDULE\" SET \"COU_ID\"=?, \"CONFIRMTEACH_TYPE_ID\"=?, \"SCH_BeginTime\"=?, \"SCH_EndTime\"=?, \"SCH_TeachDate\"=?, \"SCH_ModifyBy\"=?, \"SCH_ModifyTime\"=?");
            sqlBuffer.append("WHERE SCH_ID=?;");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setInt(1, entity.getCouId());
            pstmt.setInt(2, entity.getConfirmTeachTypeId());
            pstmt.setString(3, entity.getSchBeginTime());
            pstmt.setString(4, entity.getSchEndTime());
            pstmt.setDate(5, new java.sql.Date(entity.getSchTeachDate().getTime()));
            pstmt.setString(6, entity.getSchModifyBy());
            pstmt.setTimestamp(7, new Timestamp(new Date().getTime()));


            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;

    }

    public static String deleteSchedule(Object scheduleEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        ScheduleEntity entity = (ScheduleEntity) scheduleEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("DELETE FROM \"SCHEDULE\" WHERE SCH_ID=?");


            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String createUserType(Object userTypeEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        UserTypeEntity entity = (UserTypeEntity) userTypeEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("INSERT INTO \"USER_TYPE\" (\"USER_TYPE_ID\", \"USER_TYPE_Desc\", \"USER_TYPE_CreatedBy\", \"USER_TYPE_CreatedTime\")");
            sqlBuffer.append(" VALUES (?, ?, ?, ?)");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setInt(1, entity.getUserTypeId());
            pstmt.setString(2, entity.getUserTypeDesc());
            pstmt.setString(3, entity.getUserTypeCreatedBy());
            pstmt.setTimestamp(4, new Timestamp(new Date().getTime()));



            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String updateUserType(Object userTypeEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        UserTypeEntity entity = (UserTypeEntity) userTypeEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("UPDATE \"USER_TYPE\" SET \"USER_TYPE_Desc\"=?, \"USER_TYPE_ModifyBy\"=?, \"USER_TYPE_ModifyTime\"=?");
            sqlBuffer.append("WHERE USER_TYPE_ID=?;");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, entity.getUserTypeDesc());
            pstmt.setString(2, entity.getUserTypeModifyBy());
            pstmt.setTimestamp(3, new Timestamp(new Date().getTime()));


            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;

    }

    public static String deleteUserType(Object userTypeEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        UserTypeEntity entity = (UserTypeEntity) userTypeEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("DELETE FROM \"USER_TYPE\" WHERE USER_TYPE_ID=?");


            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static String createConfirmTeachType(Object confirmTeachTypeEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        ConfirmTeachTypeEntity entity = (ConfirmTeachTypeEntity) confirmTeachTypeEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("INSERT INTO \"CONFIRMTEACH_TYPE\" (\"CONFIRMTEACH_TYPE_ID\", \"CONFIRMTEACH_TYPE_Desc\", \"CONFIRMTEACH_TYPE_CreatedBy\", \"CONFIRMTEACH_TYPE_CreatedTime\")");
            sqlBuffer.append(" VALUES (?, ?, ?, ?)");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setInt(1, entity.getConfirmTeachTypeId());
            pstmt.setString(2, entity.getConfirmTeachTypeDesc());
            pstmt.setString(3, entity.getConfirmTeachTypeCreatedBy());
            pstmt.setTimestamp(4, new Timestamp(new Date().getTime()));



            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }
    
    public static String updateConfirmTeachType(Object confirmTeachTypeEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        ConfirmTeachTypeEntity entity = (ConfirmTeachTypeEntity) confirmTeachTypeEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("UPDATE \"CONFIRMTEACH_TYPE\" SET \"CONFIRMTEACH_TYPE_Desc\"=?, \"CONFIRMTEACH_TYPE_ModifyBy\"=?, \"CONFIRMTEACH_TYPE_ModifyTime\"=?");
            sqlBuffer.append("WHERE CONFIRMTEACH_TYPE_ID=?;");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, entity.getConfirmTeachTypeDesc());
            pstmt.setString(2, entity.getConfirmTeachTypeModifyBy());
            pstmt.setTimestamp(3, new Timestamp(new Date().getTime()));


            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;

    }
    
    public static String updateMakeupClass(MakeupclassEntity entity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();

            sqlBuffer.append("UPDATE \"MAKEUPCLASS\" SET \"LOC_ID\" = ?, \"MARC_CompenDate\" = ?, \"MARC_CompenBeginTime\" = ?, ");
            sqlBuffer.append("\"MARC_CompenEndTime\" = ?, \"MARC_Amount\" = ?, \"MARC_ModifyBy\" = ?, \"MARC_ModifyTime\" = ? ");
            sqlBuffer.append("WHERE \"MARC_ID\" = ? ");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            if(entity.getLocId() == 0) {
                pstmt.setObject(1, null);
            } else {
                pstmt.setInt(1, entity.getLocId());
            }
            pstmt.setDate(2, entity.getMarcCompenDate());
            pstmt.setString(3, entity.getMarcCompenBeginTime());
            pstmt.setString(4, entity.getMarcCompenEndTime());
            pstmt.setDouble(5, entity.getMarcAmount());
            pstmt.setString(6, entity.getMarcModifyBy());
            pstmt.setTimestamp(7, new Timestamp(new Date().getTime()));
            pstmt.setInt(8, entity.getMarcId());

            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;

    }

    public static String updateFaculty(Object entity, String userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        StringBuffer sqlBuffer = new StringBuffer();
        
        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            if(entity instanceof FacultyEntity) {
                FacultyEntity facultyEntity = (FacultyEntity)entity;

                sqlBuffer.append("UPDATE \"FACULTY\" ");
                sqlBuffer.append("SET \"FAC_Name\" = ?, \"FAC_ModifyBy\" = ?, \"FAC_ModifyTime\" = ? ");
                sqlBuffer.append("WHERE \"FAC_ID\" = ? ");

                pstmt = conn.prepareStatement(sqlBuffer.toString());
                pstmt.setString(1, facultyEntity.getFacName());
                pstmt.setString(2, userId);
                pstmt.setTimestamp(3, new Timestamp(new Date().getTime()));
                pstmt.setString(4, facultyEntity.getFacId());

                pstmt.executeUpdate();
            } else if(entity instanceof DepartmentEntity) {
                DepartmentEntity departmentEntity = (DepartmentEntity)entity;
                
                sqlBuffer.append("UPDATE \"DEPARTMENT\" ");
                sqlBuffer.append("SET \"DEP_Name\" = ?, \"DEP_ModifyBy\" = ?, \"DEP_ModifyTime\" = ? ");
                sqlBuffer.append("WHERE \"DEP_ID\" = ? ");

                pstmt = conn.prepareStatement(sqlBuffer.toString());
                pstmt.setString(1, departmentEntity.getDepName());
                pstmt.setString(2, userId);
                pstmt.setTimestamp(3, new Timestamp(new Date().getTime()));
                pstmt.setString(4, departmentEntity.getDepId());

                pstmt.executeUpdate();
            } else if(entity instanceof BranchEntity) {
                BranchEntity branchEntity = (BranchEntity)entity;
                
                sqlBuffer.append("UPDATE \"BRANCH\" ");
                sqlBuffer.append("SET \"BRA_Name\" = ?, \"BRA_ModifyBy\" = ?, \"BRA_ModifyTime\" = ? ");
                sqlBuffer.append("WHERE \"BRA_ID\" = ? ");

                pstmt = conn.prepareStatement(sqlBuffer.toString());
                pstmt.setString(1, branchEntity.getBraName());
                pstmt.setString(2, userId);
                pstmt.setTimestamp(3, new Timestamp(new Date().getTime()));
                pstmt.setString(4, branchEntity.getBraId());

                pstmt.executeUpdate();
            } else if(entity instanceof ProgramEntity) {
                ProgramEntity programEntity = (ProgramEntity)entity;
                
                sqlBuffer.append("UPDATE \"PROGRAM\" ");
                sqlBuffer.append("SET \"PRO_Name\" = ?, \"PRO_ModifyBy\" = ?, \"PRO_ModifyTime\" = ? ");
                sqlBuffer.append("WHERE \"PRO_ID\" = ? ");

                pstmt = conn.prepareStatement(sqlBuffer.toString());
                pstmt.setString(1, programEntity.getProName());
                pstmt.setString(2, userId);
                pstmt.setTimestamp(3, new Timestamp(new Date().getTime()));
                pstmt.setString(4, programEntity.getProId());

                pstmt.executeUpdate();
            } else if(entity instanceof SubjectEntity) {
                SubjectEntity subjectEntity = (SubjectEntity)entity;
                
                sqlBuffer.append("UPDATE \"SUBJECT\" ");
                sqlBuffer.append("SET \"SUB_NameThai\" = ?, \"SUB_NameEng\" = ?, \"SUB_CreditType\" = ?, \"SUB_Credit\" = ?, ");
                sqlBuffer.append("\"SUB_DescriptionThai\" = ?, \"SUB_DescriptionEng\" = ?, \"SUB_ModifyBy\" = ?, \"SUB_ModifyTime\" = ? ");
                sqlBuffer.append("WHERE \"SUB_ID\" = ? ");

                pstmt = conn.prepareStatement(sqlBuffer.toString());
                pstmt.setString(1, subjectEntity.getSubNameThai());
                pstmt.setString(2, subjectEntity.getSubNameEng());
                pstmt.setString(3, subjectEntity.getSubCreditType());
                pstmt.setString(4, subjectEntity.getSubCredit());
                pstmt.setString(5, subjectEntity.getSubDescriptionThai());
                pstmt.setString(6, subjectEntity.getSubDescriptionEng());
                pstmt.setString(7, userId);
                pstmt.setTimestamp(8, new Timestamp(new Date().getTime()));
                pstmt.setString(9, subjectEntity.getSubId());

                pstmt.executeUpdate();
            }
            
            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;

    }

    public static String deleteConfirmTeachType(Object confirmTeachTypeEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String result;
        ConfirmTeachTypeEntity entity = (ConfirmTeachTypeEntity) confirmTeachTypeEntity;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuffer sqlBuffer = new StringBuffer();


            sqlBuffer.append("DELETE FROM \"CONFIRMTEACH_TYPE\" WHERE CONFIRMTEACH_TYPE_ID=?");


            pstmt.executeUpdate();

            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
            result = "error : " + e.getMessage();
        }

        return result;
    }

    public static UserEntity selectLogin(String userName, String pwd) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        UserEntity userEntity = null;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT * ");
            sqlBuffer.append("FROM \"USER\", \"USER_TYPE\", \"DEPARTMENT\", \"FACULTY\" ");
            sqlBuffer.append("WHERE \"USER\".\"USE_Name\" = ? ");
            sqlBuffer.append("AND \"USER\".\"USE_Password\" = ?");
            sqlBuffer.append("AND \"USER\".\"USER_TYPE_ID\" = \"USER_TYPE\".\"USER_TYPE_ID\"");
            sqlBuffer.append("AND \"USER\".\"FAC_ID\" = \"FACULTY\".\"FAC_ID\" ");
            sqlBuffer.append("AND \"USER\".\"DEP_ID\" = \"DEPARTMENT\".\"DEP_ID\" ");


            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, userName);
            pstmt.setString(2, pwd);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                userEntity = new UserEntity();
                userEntity.setUseId(rs.getString("USE_ID"));
                userEntity.setUseName(rs.getString("USE_Name"));
                userEntity.setUsePassword(rs.getString("USE_Password"));
                userEntity.setUseTitle(rs.getString("USE_Tittle"));
                userEntity.setUseFirstName(rs.getString("USE_Firstname"));
                userEntity.setUseLastName(rs.getString("USE_Lastname"));
                userEntity.setFacName(rs.getString("FAC_Name"));
                userEntity.setDepName(rs.getString("DEP_Name"));
                userEntity.setUserTypeDesc(rs.getString("USER_TYPE_Desc"));
                userEntity.setUserTypeId(rs.getInt("USER_TYPE_ID"));

            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return userEntity;
    }

    public static List<UserEntity> selectUser(String param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<UserEntity> list = new ArrayList<UserEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"USER\".\"USE_ID\",\"USER\".\"USE_Tittle\", \"USER\".\"USE_Firstname\", \"USER\".\"USE_Lastname\", \"USER_TYPE\".\"USER_TYPE_Desc\"");
            sqlBuffer.append("FROM \"USER\",\"USER_TYPE\"");
            sqlBuffer.append("WHERE \"USER\".\"USE_ID\" || \"USER\".\"USE_Tittle\" || \"USER\".\"USE_Firstname\" || \"USER\".\"USE_Lastname\" || \"USER_TYPE\".\"USER_TYPE_Desc\" like '%" + param + "%' ");
            sqlBuffer.append("AND \"USER\".\"USER_TYPE_ID\"=\"USER_TYPE\".\"USER_TYPE_ID\"");



            pstmt = conn.prepareStatement(sqlBuffer.toString());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                UserEntity entity = new UserEntity();

                entity.setUseId(rs.getString("USE_ID"));
                entity.setUseTitle(rs.getString("USE_Tittle"));
                entity.setUseFirstName(rs.getString("USE_Firstname"));
                entity.setUseLastName(rs.getString("USE_Lastname"));
                entity.setUserTypeDesc(rs.getString("USER_TYPE_Desc"));

                list.add(entity);
            }

//            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return list;
    }

    public static List<UserEntity> selectUserPreview(String param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<UserEntity> list = new ArrayList<UserEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"USER\".\"USE_ID\", \"USER\".\"USE_Tittle\", \"USER\".\"USE_Firstname\", \"USE_Name\", ");
            sqlBuffer.append("\"USER\".\"USE_Lastname\", \"USER\".\"USE_Position\", \"FACULTY\".\"FAC_Name\", ");
            sqlBuffer.append("\"DEPARTMENT\".\"DEP_Name\", \"USER_TYPE\".\"USER_TYPE_Desc\", ");
            sqlBuffer.append(" \"USER\".\"FAC_ID\" , \"DEPARTMENT\".\"DEP_ID\", \"USER\".\"USER_TYPE_ID\"");
            sqlBuffer.append("FROM \"USER\", \"FACULTY\", \"DEPARTMENT\", \"USER_TYPE\" ");
            sqlBuffer.append("WHERE \"USER\".\"USE_ID\" = ? ");
            sqlBuffer.append("AND \"USER\".\"FAC_ID\" = \"FACULTY\".\"FAC_ID\" ");
            sqlBuffer.append("AND \"USER\".\"DEP_ID\" = \"DEPARTMENT\".\"DEP_ID\" ");
            sqlBuffer.append("AND \"USER\".\"USER_TYPE_ID\" = \"USER_TYPE\".\"USER_TYPE_ID\" ");



            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, param);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                UserEntity entity = new UserEntity();

                entity.setUseId(rs.getString("USE_ID"));
                entity.setUseTitle(rs.getString("USE_Tittle"));
                entity.setUseName(rs.getString("USE_NAME"));
                entity.setUseFirstName(rs.getString("USE_Firstname"));
                entity.setUseLastName(rs.getString("USE_Lastname"));
                entity.setUsePosition(rs.getString("USE_Position"));
                entity.setFacName(rs.getString("FAC_Name"));
                entity.setDepName(rs.getString("DEP_Name"));
                entity.setUserTypeDesc(rs.getString("USER_TYPE_Desc"));
                entity.setFacId(rs.getString("FAC_ID"));
                entity.setDepId(rs.getString("DEP_ID"));
                entity.setUserTypeId(rs.getInt("USER_TYPE_ID"));

                list.add(entity);
            }

//            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return list;
    }

    public static ArrayList<UserTypeEntity> selectUserType() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<UserTypeEntity> list = new ArrayList<UserTypeEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"USER_TYPE_ID\", \"USER_TYPE_Desc\" ");
            sqlBuffer.append("FROM \"USER_TYPE\" ");


            pstmt = conn.prepareStatement(sqlBuffer.toString());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                UserTypeEntity entity = new UserTypeEntity();

                entity.setUserTypeId(rs.getInt("USER_TYPE_ID"));
                entity.setUserTypeDesc(rs.getString("USER_TYPE_Desc"));

                list.add(entity);
            }



        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return list;
    }

    public static SubjectEntity findSubject(String param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        SubjectEntity entity = new SubjectEntity();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT * ");
            sqlBuffer.append("FROM \"SUBJECT\" ");
            sqlBuffer.append("WHERE \"SUB_ID\" = ? ");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, param);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                entity = new SubjectEntity();

                entity.setSubId(rs.getString("SUB_ID"));
                entity.setSubNameThai(rs.getString("SUB_NameThai"));
                entity.setSubNameEng(rs.getString("SUB_NameEng"));
                entity.setSubCreditType(rs.getString("SUB_CreditType"));
                entity.setSubCredit(rs.getString("SUB_Credit"));
                entity.setSubDescriptionThai(rs.getString("SUB_DescriptionThai"));
                entity.setSubDescriptionEng(rs.getString("SUB_DescriptionEng"));

            }



        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return entity;
    }
    
    public static ArrayList<UserEntity> findTeacher(String teachId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<UserEntity> list = new ArrayList<UserEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT * ");
            sqlBuffer.append("FROM \"USER\" ");
            sqlBuffer.append("WHERE \"USER_TYPE_ID\" = 1 ");
            
            if(!teachId.equalsIgnoreCase("")) {
             sqlBuffer.append("AND \"USE_ID\" = '"+teachId+"' ");
            }

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                UserEntity entity = new UserEntity();
        
                entity.setFacId(rs.getString("FAC_ID"));
                entity.setDepId(rs.getString("DEP_ID"));
                entity.setUseId(rs.getString("USE_ID"));
                entity.setUseName(rs.getString("USE_Name"));
                entity.setUsePassword(rs.getString("USE_Password"));
                entity.setUseTitle(rs.getString("USE_Tittle"));
                entity.setUseFirstName(rs.getString("USE_Firstname"));
                entity.setUseLastName(rs.getString("USE_Lastname"));
                entity.setUsePosition(rs.getString("USE_Position"));
                entity.setUserTypeId(Integer.parseInt(rs.getString("USER_TYPE_ID")));
                
                list.add(entity);

            }

        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return list;
    }

    public static List<SubjectEntity> selectSubjectForCourse(String param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<SubjectEntity> list = new ArrayList<SubjectEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT * ");
            sqlBuffer.append("FROM \"SUBJECT\" ");
            sqlBuffer.append("WHERE \"SUB_ID\" = '" + param + "' ");
            
            System.out.println(sqlBuffer.toString());

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                SubjectEntity entity = new SubjectEntity();

                entity.setSubId(rs.getString("SUB_ID"));
                entity.setSubNameThai(rs.getString("SUB_NameThai"));
                entity.setSubNameEng(rs.getString("SUB_NameEng"));
                entity.setSubCreditType(rs.getString("SUB_CreditType"));
                entity.setSubCredit(rs.getString("SUB_Credit"));
                entity.setSubDescriptionThai(rs.getString("SUB_DescriptionThai"));
                entity.setSubDescriptionEng(rs.getString("SUB_DescriptionEng"));


                list.add(entity);
            }



        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return list;
    }

    public static List<SubjectEntity> selectSubjectAll(String param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<SubjectEntity> list = new ArrayList<SubjectEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"SUB_ID\", \"SUB_NameThai\", \"SUB_NameEng\", \"SUB_CreditType\", \"SUB_Credit\", \"SUB_DescriptionThai\", \"SUB_DescriptionEng\" ");
            sqlBuffer.append("FROM \"SUBJECT\" ");
            sqlBuffer.append("WHERE \"PRO_ID\" = ?");



            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, param);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                SubjectEntity entity = new SubjectEntity();

                entity.setSubId(rs.getString("SUB_ID"));
                entity.setSubNameThai(rs.getString("SUB_NameThai"));
                entity.setSubNameEng(rs.getString("SUB_NameEng"));
                entity.setSubCreditType(rs.getString("SUB_CreditType"));
                entity.setSubCredit(rs.getString("SUB_Credit"));
                entity.setSubDescriptionThai(rs.getString("SUB_DescriptionThai"));
                entity.setSubDescriptionEng(rs.getString("SUB_DescriptionEng"));


                list.add(entity);
            }



        } catch (Exception e) {
            e.printStackTrace();

        }

        return list;
    }

    public static ArrayList<FacultyEntity> selectFacultyAll() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<FacultyEntity> list = new ArrayList<FacultyEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"FAC_ID\", \"FAC_Name\" ");
            sqlBuffer.append("FROM \"FACULTY\" ");



            pstmt = conn.prepareStatement(sqlBuffer.toString());

            rs = pstmt.executeQuery();
            while (rs.next()) {
                FacultyEntity entity = new FacultyEntity();

                entity.setFacId(rs.getString("FAC_ID"));
                entity.setFacName(rs.getString("FAC_Name"));

                list.add(entity);
            }



        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return list;
    }

    public static FacultyEntity findFaculty(String param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        FacultyEntity entity = new FacultyEntity();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"FAC_ID\", \"FAC_Name\" ");
            sqlBuffer.append("FROM \"FACULTY\" ");
            sqlBuffer.append("WHERE \"FAC_ID\" = ? ");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, param);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                entity = new FacultyEntity();

                entity.setFacId(rs.getString("FAC_ID"));
                entity.setFacName(rs.getString("FAC_Name"));
                
            }

        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return entity;
    }

    public static List<DepartmentEntity> selectDepartmentAll(String param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<DepartmentEntity> list = new ArrayList<DepartmentEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"DEP_ID\", \"DEP_Name\" ");
            sqlBuffer.append("FROM \"DEPARTMENT\" ");
            sqlBuffer.append("WHERE \"FAC_ID\" = ?");


            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, param);


            rs = pstmt.executeQuery();
            while (rs.next()) {
                DepartmentEntity entity = new DepartmentEntity();

                entity.setDepId(rs.getString("DEP_ID"));
                entity.setDepName(rs.getString("DEP_Name"));


                list.add(entity);
            }



        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return list;
    }

    public static DepartmentEntity findDepartment(String param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        DepartmentEntity entity = new DepartmentEntity();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"DEP_ID\", \"DEP_Name\" ");
            sqlBuffer.append("FROM \"DEPARTMENT\" ");
            sqlBuffer.append("WHERE \"DEP_ID\" = ? ");
 
            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, param);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                entity = new DepartmentEntity();

                entity.setDepId(rs.getString("DEP_ID"));
                entity.setDepName(rs.getString("DEP_Name"));

            }    



        } catch (Exception e) {
            e.printStackTrace();

        }

        return entity;
    }

    public static List<BranchEntity> selectBranchAll(String param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<BranchEntity> list = new ArrayList<BranchEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"BRA_ID\", \"BRA_Name\" ");
            sqlBuffer.append("FROM \"BRANCH\" ");
            sqlBuffer.append("WHERE \"DEP_ID\" = ?");


            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, param);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                BranchEntity entity = new BranchEntity();

                entity.setBraId(rs.getString("BRA_ID"));
                entity.setBraName(rs.getString("BRA_Name"));


                list.add(entity);
            }



        } catch (Exception e) {
            e.printStackTrace();

        }

        return list;
    }
    
    public static SemesterEntity findSemester(SemesterEntity entity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT * ");
            sqlBuffer.append("FROM \"SEMESTER\" ");
            sqlBuffer.append("WHERE \"SEM_ID\" = ? ");


            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setInt(1, entity.getSemId());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                entity = new SemesterEntity();

                entity.setSemId(rs.getInt("SEM_ID"));
                entity.setSemAcademicYear(rs.getString("SEM_AcademicYear"));
                entity.setSemSemester(rs.getString("SEM_Semester"));
                entity.setSemCreatedBy(rs.getString("SEM_CreatedBy"));
                entity.setSemCreatedTime(rs.getTimestamp("SEM_CreatedTime"));
                entity.setSemModifyBy(rs.getString("SEM_ModifyBy"));
                entity.setSemModifyTime(rs.getTimestamp("SEM_ModifyTime"));
                
            }

        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return entity;
    }
    
    public static ArrayList<ScheduleEntity> findSchedule(ScheduleEntity scheduleEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<ScheduleEntity> list = new ArrayList<ScheduleEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"SCHEDULE\".*, \"COURSE_USER\".\"USE_ID\", \"USER\".\"USE_Tittle\", \"USER\".\"USE_Firstname\", ");
            sqlBuffer.append("\"USER\".\"USE_Lastname\", \"CONFIRMCLASS\".\"CONF_ID\", \"MAKEUPCLASS\".\"MARC_ID\" ");
            sqlBuffer.append("FROM \"COURSE_USER\", \"USER\", \"SCHEDULE\" ");
            sqlBuffer.append("LEFT JOIN \"CONFIRMCLASS\" ON \"SCHEDULE\".\"SCH_ID\" = \"CONFIRMCLASS\".\"SCH_ID\" ");
            sqlBuffer.append("LEFT JOIN \"MAKEUPCLASS\" ON (\"SCHEDULE\".\"SCH_ID\" = \"MAKEUPCLASS\".\"SCH_ID\") ");
            sqlBuffer.append("WHERE \"COURSE_USER\".\"COU_USE_ID\" = \"SCHEDULE\".\"COU_USE_ID\" AND \"SCHEDULE\".\"COU_ID\" = ? ");
            sqlBuffer.append("AND \"COURSE_USER\".\"USE_ID\" = \"USER\".\"USE_ID\" ");
            sqlBuffer.append("ORDER BY \"SCHEDULE\".\"SCH_TeachDate\" ");

            System.out.println(sqlBuffer.toString());
            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setInt(1, scheduleEntity.getCouId());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                ScheduleEntity entity = new ScheduleEntity();
                
                entity.setSchId(rs.getInt("SCH_ID"));
                entity.setCouId(rs.getInt("COU_ID"));
                entity.setSchBeginTime(rs.getString("SCH_BeginTime"));
                entity.setSchEndTime(rs.getString("SCH_EndTime"));
                entity.setSchTeachDate(rs.getDate("SCH_TeachDate"));
                entity.setSchCreatedBy(rs.getString("SCH_CreatedBy"));
                entity.setSchCreatedTime(rs.getTimestamp("SCH_CreatedTime"));
                entity.setSchModifyBy(rs.getString("SCH_ModifyBy"));
                entity.setSchModifyTime(rs.getTimestamp("SCH_ModifyTime"));
                entity.setConfirmTeachTypeId(rs.getInt("CONFIRMTEACH_TYPE_ID"));
                entity.setCouUseId(String.valueOf(rs.getInt("COU_USE_ID")));
                entity.setUserId(rs.getString("USE_ID"));
                entity.setUseTittle(rs.getString("USE_Tittle"));
                entity.setUseFirstname(rs.getString("USE_Firstname"));
                entity.setUseLastname(rs.getString("USE_Lastname"));
                entity.setConfId(rs.getInt("CONF_ID"));
                entity.setMarcId(rs.getInt("MARC_ID"));
//                System.out.println("getCouUseId = "+entity.getCouUseId());
                list.add(entity);
                
            }

        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return list;
    }
    
    public static ArrayList<UserEntity> findCourseUseDistinct(CourseEntity courseEntity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<UserEntity> list = new ArrayList<UserEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT DISTINCT \"USER\".\"USE_ID\", \"USER\".\"USE_Tittle\", \"USER\".\"USE_Firstname\", \"USER\".\"USE_Lastname\" ");
            sqlBuffer.append("FROM \"COURSE_USER\", \"USER\" ");
            sqlBuffer.append("WHERE \"COURSE_USER\".\"USE_ID\" = \"USER\".\"USE_ID\" AND \"COU_ID\" = ? ");


            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setInt(1, courseEntity.getCouId());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                UserEntity entity = new UserEntity();

                entity.setUseId(rs.getString("USE_ID"));
                entity.setUseTitle(rs.getString("USE_Tittle"));
                entity.setUseFirstName(rs.getString("USE_Firstname"));
                entity.setUseLastName(rs.getString("USE_Lastname"));
                
                list.add(entity);
                
            }

        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return list;
    }
    
    public static CourseEntity findCourse(CourseEntity entity) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT * ");
            sqlBuffer.append("FROM \"COURSE\" ");
            sqlBuffer.append("WHERE \"COU_ID\" = ? ");


            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setInt(1, entity.getCouId());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                entity = new CourseEntity();

                entity.setCouId(rs.getInt("COU_ID"));
                entity.setSubId(rs.getString("SUB_ID"));
                entity.setSemId(rs.getInt("SEM_ID"));
                entity.setLocId(rs.getInt("LOC_ID"));
                entity.setCouBeginDate(rs.getDate("COU_BeginDate"));
                entity.setCouEndDate(rs.getDate("COU_EndDate"));
                entity.setCouDaySun(rs.getString("COU_DaySun"));
                entity.setCouDayMon(rs.getString("COU_DayMon"));
                entity.setCouDayTue(rs.getString("COU_DayTue"));
                entity.setCouDayWed(rs.getString("COU_DayWed"));
                entity.setCouDayThu(rs.getString("COU_DayThu"));
                entity.setCouDayFri(rs.getString("COU_DayFri"));
                entity.setCouDaySat(rs.getString("COU_DaySat"));
                entity.setCouBeginTime(rs.getString("COU_BeginTime"));
                entity.setCouEndTime(rs.getString("COU_EndTime"));
                entity.setCouDuration(rs.getString("COU_Duration"));
                entity.setCouPricePerCourse(rs.getDouble("COU_PricePerCourse"));
                entity.setCouPricePerTime(rs.getDouble("COU_PricePerTime"));
                entity.setCouCreatedBy(rs.getString("COU_CreatedBy"));
                entity.setCouCreatedTime(rs.getTimestamp("COU_CreatedTime"));
                entity.setCouModifyBy(rs.getString("COU_ModifyBy"));
                entity.setCouModifyTime(rs.getTimestamp("COU_ModifyTime"));
                
            }

        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return entity;
    }
    
    public static BranchEntity findBranch(String param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        BranchEntity entity = new BranchEntity();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"BRA_ID\", \"BRA_Name\" ");
            sqlBuffer.append("FROM \"BRANCH\" ");
            sqlBuffer.append("WHERE \"BRA_ID\" = ?");


            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, param);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                entity = new BranchEntity();

                entity.setBraId(rs.getString("BRA_ID"));
                entity.setBraName(rs.getString("BRA_Name"));

            }

        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return entity;
    }
    
    public static MakeupclassEntity findMakeupClass(int marcId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        MakeupclassEntity entity = null;

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT * ");
            sqlBuffer.append("FROM \"MAKEUPCLASS\" ");
            sqlBuffer.append("WHERE \"MARC_ID\" = ? ");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setInt(1, marcId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                entity = new MakeupclassEntity();

                entity.setMarcId(rs.getInt("MARC_ID"));
                entity.setLocId(rs.getInt("LOC_ID"));
                entity.setMarcCompenDate(rs.getDate("MARC_CompenDate"));
                entity.setMarcCompenBeginTime(rs.getString("MARC_CompenBeginTime"));
                entity.setMarcCompenEndTime(rs.getString("MARC_CompenEndTime"));
                entity.setMarcAmount(rs.getDouble("MARC_Amount"));

            }

        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return entity;
    }

    public static ArrayList<SemesterEntity> findConfirmClass(String param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<SemesterEntity> list = new ArrayList<SemesterEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"COURSE\".\"SUB_ID\", \"SUBJECT\".\"SUB_NameEng\", \"SEMESTER\".\"SEM_AcademicYear\", ");
            sqlBuffer.append("\"COURSE\".\"COU_ID\", \"SEMESTER\".\"SEM_Semester\" ");
            sqlBuffer.append("FROM \"COURSE\", \"SEMESTER\", \"SUBJECT\" ");
            sqlBuffer.append("WHERE \"COURSE\".\"SEM_ID\" = \"SEMESTER\".\"SEM_ID\" ");
            sqlBuffer.append("AND \"COURSE\".\"SUB_ID\" = \"SUBJECT\".\"SUB_ID\" ");
            sqlBuffer.append("AND \"COURSE\".\"SUB_ID\"||\"SUBJECT\".\"SUB_NameEng\"||\"SEMESTER\".\"SEM_AcademicYear\" LIKE '%"+param+"%' ");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                SemesterEntity entity = new SemesterEntity();

                entity.setCouId(rs.getInt("COU_ID"));
                entity.setSubId(rs.getString("SUB_ID"));
                entity.setSubNameEng(rs.getString("SUB_NameEng"));
                entity.setSemAcademicYear(rs.getString("SEM_AcademicYear"));
                entity.setSemSemester(rs.getString("SEM_Semester"));
                
                list.add(entity);

            }

        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return list;
    }
    
        public static ArrayList<SemesterEntity> findConfirmClassT(String param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<SemesterEntity> list = new ArrayList<SemesterEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT DISTINCT \"COURSE\".\"SUB_ID\", \"SUBJECT\".\"SUB_NameEng\", \"SEMESTER\".\"SEM_AcademicYear\", ");
            sqlBuffer.append("\"COURSE\".\"COU_ID\", \"SEMESTER\".\"SEM_Semester\" ");
            sqlBuffer.append("FROM \"COURSE\", \"SEMESTER\", \"SUBJECT\", \"COURSE_USER\" ");
            sqlBuffer.append("WHERE \"COURSE\".\"SEM_ID\" = \"SEMESTER\".\"SEM_ID\" ");
            sqlBuffer.append("AND \"COURSE\".\"SUB_ID\" = \"SUBJECT\".\"SUB_ID\" ");
            sqlBuffer.append("AND \"COURSE\".\"COU_ID\" = \"COURSE_USER\".\"COU_ID\" ");
            sqlBuffer.append("AND \"COURSE_USER\".\"USE_ID\" = ? ");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, param);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                SemesterEntity entity = new SemesterEntity();

                entity.setCouId(rs.getInt("COU_ID"));
                entity.setSubId(rs.getString("SUB_ID"));
                entity.setSubNameEng(rs.getString("SUB_NameEng"));
                entity.setSemAcademicYear(rs.getString("SEM_AcademicYear"));
                entity.setSemSemester(rs.getString("SEM_Semester"));
                
                list.add(entity);

            }

        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return list;
    }

    public static List<ProgramEntity> selectProgramAll(String param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<ProgramEntity> list = new ArrayList<ProgramEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"PRO_ID\", \"PRO_Name\" ");
            sqlBuffer.append("FROM \"PROGRAM\" ");
            sqlBuffer.append("WHERE \"BRA_ID\" = ?");


            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, param);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                ProgramEntity entity = new ProgramEntity();

                entity.setProId(rs.getString("PRO_ID"));
                entity.setProName(rs.getString("PRO_Name"));


                list.add(entity);
            }



        } catch (Exception e) {
            e.printStackTrace();

        }

        return list;
    }

    public static ProgramEntity findProgram(String param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ProgramEntity entity = new ProgramEntity();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"PRO_ID\",\"PRO_Name\" ");
            sqlBuffer.append("FROM \"PROGRAM\" ");
            sqlBuffer.append("WHERE \"PRO_ID\" = ? ");


            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, param);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                entity = new ProgramEntity();

                entity.setProId(rs.getString("PRO_ID"));
                entity.setProName(rs.getString("PRO_Name"));

            }



        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return entity;
    }

    
    
    public static List<LocationEntity> selectLocation(String param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<LocationEntity> list = new ArrayList<LocationEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"LOC_ID\",\"LOC_Room\" ");
            sqlBuffer.append("FROM \"LOCATION\" ");
            sqlBuffer.append("WHERE \"LOC_Room\" || \"LOC_ID\" ");
            sqlBuffer.append("like '%" + param + "%' ");


            pstmt = conn.prepareStatement(sqlBuffer.toString());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                LocationEntity entity = new LocationEntity();
                
                entity.setLocId(rs.getInt("LOC_ID"));
                entity.setLocRoom(rs.getString("LOC_Room"));

                list.add(entity);
            }



        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return list;
    }

    public static ArrayList<LocationEntity> selectLocation() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<LocationEntity> list = new ArrayList<LocationEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"LOC_ID\", \"LOC_Room\" ");
            sqlBuffer.append("FROM \"LOCATION\" ");


            pstmt = conn.prepareStatement(sqlBuffer.toString());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                LocationEntity entity = new LocationEntity();

                entity.setLocId(rs.getInt("LOC_ID"));
                entity.setLocRoom(rs.getString("LOC_Room"));

                list.add(entity);
            }



        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return list;
    }

    public static List<CourseEntity> selectCourse(String param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<CourseEntity> list = new ArrayList<CourseEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"COURSE\".\"COU_ID\", \"SUBJECT\".\"SUB_ID\", \"SUBJECT\".\"SUB_NameEng\", \"SEMESTER\".\"SEM_AcademicYear\", \"SEMESTER\".\"SEM_Semester\"");
            sqlBuffer.append("FROM \"COURSE\", \"SUBJECT\", \"SEMESTER\"");
            sqlBuffer.append("WHERE \"COU_ID\" || \"SUBJECT\".\"SUB_ID\" || \"SUBJECT\".\"SUB_NameEng\" || \"SEMESTER\".\"SEM_AcademicYear\" || \"SEMESTER\".\"SEM_Semester\" like '%" + param + "%'");
            sqlBuffer.append("AND \"COURSE\".\"SUB_ID\"=\"SUBJECT\".\"SUB_ID\"");
            sqlBuffer.append("AND \"COURSE\".\"SEM_ID\"=\"SEMESTER\".\"SEM_ID\"");



            pstmt = conn.prepareStatement(sqlBuffer.toString());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                CourseEntity entity = new CourseEntity();

                entity.setCouId(rs.getInt("COU_ID"));
                entity.setSubId(rs.getString("SUB_ID"));
                entity.setSubNameEng(rs.getString("SUB_NameEng"));
                entity.setSemAcademicYear(rs.getString("SEM_AcademicYear"));
                entity.setSemSemester(rs.getString("SEM_Semester"));


                list.add(entity);
            }



        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return list;
    }

    public static List<CourseEntity> selectCoursePreview(String param) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<CourseEntity> list = new ArrayList<CourseEntity>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"COURSE\".\"COU_ID\", \"SUBJECT\".\"SUB_ID\", \"SUBJECT\".\"SUB_NameThai\", \"SUBJECT\".\"SUB_NameEng\", \"SUBJECT\".\"SUB_CreditType\", \"SUBJECT\".\"SUB_Credit\",");
            sqlBuffer.append("\"SEMESTER\".\"SEM_AcademicYear\", \"SEMESTER\".\"SEM_Semester\", \"COURSE\".\"COU_BeginDate\", \"COURSE\".\"COU_EndDate\",");
            sqlBuffer.append("\"COURSE\".\"COU_DaySun\", \"COURSE\".\"COU_DayMon\", \"COURSE\".\"COU_DayTue\", \"COURSE\".\"COU_DayWed\", \"COURSE\".\"COU_DayThu\",");
            sqlBuffer.append("\"COURSE\".\"COU_DayFri\", \"COURSE\".\"COU_DaySat\", \"COURSE\".\"COU_BeginTime\", \"COURSE\".\"COU_EndTime\", \"COURSE\".\"COU_Duration\",");
            sqlBuffer.append("\"COURSE\".\"COU_PricePerCourse\", \"LOC_Room\", \"USER\".\"USE_Tittle\", \"USER\".\"USE_Firstname\", \"USER\".\"USE_Lastname\"");
            sqlBuffer.append("FROM \"COURSE\", \"USER\", \"SUBJECT\", \"LOCATION\", \"SEMESTER\", \"COURSE_USER\"");
            sqlBuffer.append("WHERE \"COURSE\".\"COU_ID\"='?'");
            sqlBuffer.append("AND \"COURSE\".\"SUB_ID\"=\"SUBJECT\".\"SUB_ID\"");
            sqlBuffer.append("AND \"COURSE\".\"SEM_ID\"=\"SEMESTER\".\"SEM_ID\"");
            sqlBuffer.append("AND \"COURSE\".\"LOC_ID\"=\"LOCATION\".\"LOC_ID\"");
            sqlBuffer.append("AND \"COURSE_USER\".\"USE_ID\"=\"USER\".\"USE_ID\"");

            pstmt = conn.prepareStatement(sqlBuffer.toString());
            pstmt.setString(1, param);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                CourseEntity entity = new CourseEntity();

                entity.setCouId(rs.getInt("COU_ID"));
                entity.setSubId(rs.getString("SUB_ID"));
                entity.setSubNameThai(rs.getString("SUB_NameThai"));
                entity.setSubNameEng(rs.getString("SUB_NameEng"));
                entity.setSubCreditType(rs.getString("SUB_CreditType"));
                entity.setSubCredit(rs.getString("SUB_Credit"));
                entity.setSemSemester(rs.getString("SEM_Semester"));
                entity.setSemAcademicYear(rs.getString("SEM_AcademicYear"));
                entity.setCouBeginDate(rs.getDate("COU_BeginDate"));
                entity.setCouEndDate(rs.getDate("COU_EndDate"));
                entity.setCouDaySun(rs.getString("COU_DaySun"));
                entity.setCouDayMon(rs.getString("COU_DayMon"));
                entity.setCouDayTue(rs.getString("COU_DayTue"));
                entity.setCouDayWed(rs.getString("COU_DayWed"));
                entity.setCouDayThu(rs.getString("COU_DayThu"));
                entity.setCouDayFri(rs.getString("COU_DayFri"));
                entity.setCouDaySat(rs.getString("COU_DaySat"));
                entity.setCouBeginTime(rs.getString("COU_BeginTime"));
                entity.setCouEndTime(rs.getString("COU_EndTime"));
                entity.setCouDuration(rs.getString("COU_Duration"));
                entity.setLocRoom(rs.getString("LOC_Room"));
                entity.setCouPricePerCourse(rs.getDouble("COU_PricePerCourse"));
                entity.setUseTitle(rs.getString("USE_Tittle"));
                entity.setUseFirstName(rs.getString("USE_Firstname"));
                entity.setUseLastName(rs.getString("USE_Lastname"));


                list.add(entity);
            }

        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return list;
    }
    
//        public static List<ScheduleEntity> selectSchedule(String param) {
//        Connection conn = null;
//        PreparedStatement pstmt = null;
//        ResultSet rs = null;
//        List<ScheduleEntity> list = new ArrayList<ScheduleEntity>();
//
//        try {
//            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
//            StringBuilder sqlBuffer = new StringBuilder();
//
//            sqlBuffer.append("SELECT \"SUBJECT\".\"SUB_ID\",  \"SUBJECT\".\"SUB_NameEng\" ");
//            sqlBuffer.append("FROM \"SCHEDULE\",\"SUBJECT\", \"COURSE\" ");
//            sqlBuffer.append("WHERE \"SUBJECT\".\"SUB_ID\" like '%" + param + "%' ");
//            sqlBuffer.append("AND \"SUBJECT\".\"SUB_ID\"=\"COURSE\".\"SUB_ID\" AND \"COURSE\".\"COU_ID\"=\"SCHEDULE\".\"COU_ID\" ");
//
//
//            pstmt = conn.prepareStatement(sqlBuffer.toString());
//            rs = pstmt.executeQuery();
//            while (rs.next()) {
//                ScheduleEntity entity = new ScheduleEntity();
//
//                entity.setSubId(rs.getString("SUB_ID"));
//                entity.setSubNameEng(rs.getString("SUB_NameEng"));
//
//                list.add(entity);
//            }
//
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//
//        return list;
//    }
    
    public static UserEntity getUserByID(String as_userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        UserEntity lo_user = new UserEntity();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"USER\".\"USE_ID\",\"USER\".\"USE_Tittle\",\"USER\".\"USE_Position\", \"USER\".\"USE_Firstname\", \"USER\".\"USE_Lastname\", \"USER_TYPE\".\"USER_TYPE_Desc\",\"FACULTY\".\"FAC_Name\",\"DEPARTMENT\".\"DEP_Name\" ");
            sqlBuffer.append("FROM \"USER\",\"USER_TYPE\",\"DEPARTMENT\",\"FACULTY\" ");
            sqlBuffer.append("WHERE \"USER\".\"USE_ID\"='" + as_userId + "' ");
            sqlBuffer.append("AND \"USER_TYPE\".\"USER_TYPE_ID\"=\"USER\".\"USER_TYPE_ID\" ");
            sqlBuffer.append("AND \"USER\".\"DEP_ID\"=\"DEPARTMENT\".\"DEP_ID\" ");
            sqlBuffer.append("AND \"USER\".\"FAC_ID\"=\"FACULTY\".\"FAC_ID\"; ");
            pstmt = conn.prepareStatement(sqlBuffer.toString());
            System.out.println(sqlBuffer.toString());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                UserEntity entity = new UserEntity();
                entity.setUseId(rs.getString("USE_ID"));
                entity.setUseTitle(rs.getString("USE_Tittle"));
                entity.setUseFirstName(rs.getString("USE_Firstname"));
                entity.setUseLastName(rs.getString("USE_Lastname"));
                entity.setUserTypeDesc(rs.getString("USER_TYPE_Desc"));
                entity.setUsePosition(rs.getString("USE_Position"));
                entity.setFacName(rs.getString("FAC_Name"));
                entity.setDepName(rs.getString("DEP_Name"));
                lo_user = entity;
            }

//            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return lo_user;
    }
    public static SemesterEntity getSemesterByID(int ai_semesterId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        SemesterEntity lo_sem = new SemesterEntity();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT \"SEMESTER\".\"SEM_AcademicYear\",\"SEMESTER\".\"SEM_Semester\" ");
           
            sqlBuffer.append("FROM \"SEMESTER\" ");
            sqlBuffer.append("WHERE \"SEMESTER\".\"SEM_ID\"= "+ai_semesterId+" ");
             pstmt = conn.prepareStatement(sqlBuffer.toString());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                SemesterEntity entity = new SemesterEntity();

                entity.setSemAcademicYear(rs.getString("SEM_AcademicYear"));
                entity.setSemSemester(rs.getString("SEM_Semester"));
                lo_sem = entity;
            }

//            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return lo_sem;
    }
    
    public static SemesterEntity getSemesterByDate(String ai_semesterDate) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        SemesterEntity lo_sem = new SemesterEntity();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT SEM.\"SEM_AcademicYear\",SEM.\"SEM_Semester\" ");
            sqlBuffer.append("FROM \"SEMESTER\" AS SEM ");
//            sqlBuffer.append("WHERE \"SEMESTER\".\"SEM_ID\"= '"+ai_semesterDate.getYear()+"-"+ai_semesterDate.getMonth()+"-"+ai_semesterDate.getDate()+"' ");
//            sqlBuffer.append("WHERE '"+ai_semesterDate.getYear()+"-"+ai_semesterDate.getMonth()+"-"+ai_semesterDate.getDate()+"' BETWEEN SEM.\"SEM_StartDate\" AND SEM.\"SEM_EndDate\" "); 
            sqlBuffer.append("WHERE '"+ai_semesterDate+"' BETWEEN SEM.\"SEM_StartDate\" AND SEM.\"SEM_EndDate\" ");
            sqlBuffer.append("LIMIT 1 "); 
            pstmt = conn.prepareStatement(sqlBuffer.toString());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                SemesterEntity entity = new SemesterEntity();

                entity.setSemAcademicYear(rs.getString("SEM_AcademicYear"));
                entity.setSemSemester(rs.getString("SEM_Semester"));
                lo_sem = entity;
            }

//            result = "successful.";

        } catch (Exception e) {
            e.printStackTrace();
//            result = "error : " + e.getMessage();
        }

        return lo_sem;
    }
    public static ArrayList<ScheduleTable> selectScheduleTable(String as_userId,int ai_NoWeekOfYear,int ai_year) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<ScheduleTable> list = new ArrayList<ScheduleTable>();

        try {
            conn = PgConnect.getConnection("caa_database", "postgres", "admin");
            StringBuilder sqlBuffer = new StringBuilder();

            sqlBuffer.append("SELECT EXTRACT(WEEK FROM SCH.\"SCH_TeachDate\") AS NoWeekOfYear,EXTRACT(DOW FROM SCH.\"SCH_TeachDate\") AS NoDayOfWeek,COU.\"COU_ID\",SUB.\"SUB_ID\",SUB.\"SUB_NameEng\",SUB.\"SUB_NameThai\",LOC.\"LOC_Room\",SCH.\"SCH_TeachDate\",SCH.\"SCH_BeginTime\",SCH.\"SCH_EndTime\" ");
            sqlBuffer.append("FROM \"USER\" AS US ");
            sqlBuffer.append("JOIN \"COURSE_USER\" AS CUSER ON US.\"USE_ID\" = CUSER.\"USE_ID\" ");
            sqlBuffer.append("JOIN \"COURSE\" AS COU ON COU.\"COU_ID\" = CUSER.\"COU_ID\" ");
            sqlBuffer.append("JOIN \"SCHEDULE\" AS SCH ON SCH.\"COU_ID\" = COU.\"COU_ID\" ");
            sqlBuffer.append("JOIN \"LOCATION\" AS LOC ON LOC.\"LOC_ID\" = COU.\"LOC_ID\" ");
            sqlBuffer.append("JOIN \"SUBJECT\" AS SUB ON SUB.\"SUB_ID\" = COU.\"SUB_ID\" ");    
            sqlBuffer.append("JOIN \"SEMESTER\" AS SEM ON COU.\"SEM_ID\" = SEM.\"SEM_ID\" ");  
            sqlBuffer.append("WHERE US.\"USE_ID\" = '");
            sqlBuffer.append(as_userId);
            sqlBuffer.append("' AND EXTRACT(WEEK FROM SCH.\"SCH_TeachDate\") = ");
            sqlBuffer.append(ai_NoWeekOfYear);
//            sqlBuffer.append(" AND SEM.\"SEM_ID\" = ");
            sqlBuffer.append(" AND EXTRACT(YEAR FROM SCH.\"SCH_TeachDate\") = ");
            sqlBuffer.append(ai_year);
            sqlBuffer.append(" ORDER BY NoDayOfWeek,SCH.\"SCH_TeachDate\",SCH.\"SCH_BeginTime\",SCH.\"SCH_EndTime\" ASC;");

            System.out.println(sqlBuffer.toString());
            pstmt = conn.prepareStatement(sqlBuffer.toString());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                ScheduleTable lo_schedule = new ScheduleTable();
                lo_schedule.setCOU_ID(rs.getInt("COU_ID"));
                lo_schedule.setLOC_Room(rs.getString("LOC_Room"));
                lo_schedule.setSCH_BeginTime(rs.getString("SCH_BeginTime"));
                lo_schedule.setSCH_EndTime(rs.getString("SCH_EndTime"));
                lo_schedule.setSCH_TeachDate(rs.getDate("SCH_TeachDate"));
                lo_schedule.setSUB_ID(rs.getString("SUB_ID"));
                lo_schedule.setSUB_NameEng(rs.getString("SUB_NameEng"));
                lo_schedule.setSUB_NameThai(rs.getString("SUB_NameThai"));
                lo_schedule.setNoDayOfWeek(rs.getInt("NoDayOfWeek"));
                lo_schedule.setNoWeekOfYear(rs.getInt("NoWeekOfYear"));
                
                
                String format = "yyyy-MM-dd hh:mm";
                DateFormat sdf = new SimpleDateFormat(format);
//                sdf.setTimeZone((TimeZone.getTimeZone("GMT")));
                Date d_teachDate = rs.getDate("SCH_TeachDate");
                
                String lt_BeginTime = rs.getString("SCH_BeginTime");
                String lt_EndTime = rs.getString("SCH_EndTime");
                String s_b = lt_BeginTime.split("\\.")[0];
                System.out.println("s_b = "+s_b);
                String s_e = lt_EndTime.split("\\.")[0];
                System.out.println("s_e = "+s_e);
                int diffInHours =  (int) (Double.valueOf(s_e)-Double.valueOf(s_b));
                lo_schedule.setNoBeginTime(s_b);
                lo_schedule.setNoEndTime(s_e);
                System.out.println("diffInHours = "+diffInHours);
                lo_schedule.setDifTime(String.valueOf(diffInHours));
                list.add(lo_schedule);
                System.out.println(lo_schedule.toString());
            }
            
            
//            list = ScheduleTable.setResultToVB(rs);


        } catch (Exception e) {
            e.printStackTrace();

        }

        return list;
    }

}
